/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.ntb.models.impl;

import com.ntb.entities.PermitDetails;
import com.ntb.common.DatabaseConnection;
import com.ntb.models.PermitDetailDAO;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;


/**
 *
 * @author DATBP
 */
public class PermitDetailDAOImpl extends DatabaseConnection implements PermitDetailDAO{

    public Iterator<PermitDetails> getList() {
         try {
            ArrayList arr = new ArrayList();
            openConnection();
            ResultSet rs = getResultset("Select * from PermitDetails");
            while (rs.next()) {
                PermitDetails permit=new PermitDetails();
                permit.setPermitID(rs.getInt("PermitID")); 
                permit.setIssuerName(rs.getString("IssuerName")); 
                permit.setIssuedDate(rs.getDate("IssuedDate")); 
                permit.setSignerName(rs.getString("SignerName")); 
                permit.setDescription(rs.getString("Description")); 
                permit.setStatus(rs.getString("Status")); 
                arr.add(permit);
            }
            closeConnection();
            return arr.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public Iterator<PermitDetails> getList(int page, int pageSize) {
       try {
            ArrayList arr = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_pagePermitDetails(?,?)}");
            cst.setInt(1, page);
            cst.setInt(2, pageSize);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                PermitDetails permit=new PermitDetails();
                permit.setPermitID(rs.getInt("PermitID")); 
                permit.setIssuerName(rs.getString("IssuerName")); 
                permit.setIssuedDate(rs.getDate("IssuedDate")); 
                permit.setSignerName(rs.getString("SignerName")); 
                permit.setDescription(rs.getString("Description")); 
                permit.setStatus(rs.getString("Status")); 
                arr.add(permit);
            }
            closeConnection();
            return arr.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int getPageCount(int pageSize) {
         try {
            int result = 0, count = 0;
            openConnection();
            ResultSet rs = getResultset("Select Count=Count(*) From PermitDetails");
            if (rs.next()) {
                count = rs.getInt("Count");
            }
            closeConnection();
            int temp = count % pageSize;
            if (temp != 0) {
                result = count / pageSize + 1;
            } else {
                result = count / pageSize;
            }
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public PermitDetails getRow(int id) {
         try {
            openConnection();
            PermitDetails permit = new PermitDetails();
            String sql = "select * from PermitDetails where PermitID=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                 permit.setPermitID(rs.getInt("PermitID")); 
                permit.setIssuerName(rs.getString("IssuerName")); 
                permit.setIssuedDate(rs.getDate("IssuedDate")); 
                permit.setSignerName(rs.getString("SignerName")); 
                permit.setDescription(rs.getString("Description")); 
                permit.setStatus(rs.getString("Status"));
            }
            closeConnection();
            return permit;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int add(PermitDetails object) {
         try {
            openConnection();
            String sql = "insert PermitDetails(IssuerName, IssuedDate, SignerName, Description) values(?, ?, ?, ?)";
            PreparedStatement pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pst.setString(1, object.getIssuerName()); 
            pst.setDate(2,object.getIssuedDate());
            pst.setString(3,object.getSignerName());
            pst.setString(4, object.getDescription());
            
            pst.executeUpdate();
            
            ResultSet rs = pst.getGeneratedKeys();
            rs.next();
            
            int result = rs.getInt(1);
            
            closeConnection();
            return result;
         }catch(SQLException ex){
         ex.printStackTrace();
         }
         return -1;
    }

    public int update(PermitDetails object) {
          try {
            openConnection();
            String sql = "update PermitDetails set IssuerName=?, IssuedDate=?, " +
            "SignerName=?, Description=? where PermitID=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, object.getIssuerName()); 
            pst.setDate(2,object.getIssuedDate());
            pst.setString(3,object.getSignerName());
            pst.setString(4, object.getDescription());
            pst.setInt(5, object.getPermitID());
            int rs=pst.executeUpdate();
            closeConnection();
            return rs;
         }catch(SQLException ex){
         ex.printStackTrace();
         }
         return -1;
    }

    public int delete(int id) {
        try {
            openConnection();
            String sql = "delete PermitDetails where PermitID=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1,id);
            int rs=pst.executeUpdate();
            closeConnection();
            return rs;
         }catch(SQLException ex){
         ex.printStackTrace();
         }
         return -1;
    }

    public int deleteIn(String arrayIds) {
         try {
            openConnection();
            String sql = "delete PermitDetails where PermitI in (" + arrayIds + ")";
            Statement st = conn.createStatement();
            int result = st.executeUpdate(sql);
            closeConnection();
            return result;
         }catch(SQLException ex){
         ex.printStackTrace();
         }
         return -1;
    }

}
